"""
Case Type   : nan
Case Name   : 带索引cast()函数性能测试
Create At   : 2025/6/17
Owner       : @m0_68540079
Description :
    1、准备数据
    2、关闭enable_seqscan
    3、执行单次查询
    4、循环执行10w次查询，记录耗时
    5、清理环境
Expect      :
    1、成功
    2、成功
    3、成功
    4、成功
    5、成功
History     :
"""
import os
import re
import unittest
from yat.test import Node
from yat.test import macro
from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger

class totimestampTest(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.pri_user = Node('PrimaryDbUser')
        self.constant = Constant()
        self.common = Common()
        self.tb_name = 't_tbf_0028'
        self.index_name = 'i_tbf_0028'

    def test_totimestamp(self):
        text = '-----step1:准备数据;expect:成功-----'
        self.log.info(text)
        sql = f'''drop table if exists {self.tb_name} cascade;
        create table {self.tb_name} (time1 timestamp, time2 timestamp,
        time3 timestamp, time4 timestamp);
        create index {self.index_name} on {self.tb_name} (time1);
        insert into {self.tb_name} values(TO_TIMESTAMP('WRONG' DEFAULT '20-Sep-01 01:00:00' ON CONVERSION ERROR,'YYYY-mon-DD HH24:MI:SS.FF'), TO_TIMESTAMP('WRONG' DEFAULT '21-Sep-01 01:00:00' ON CONVERSION ERROR,'YYYY-mon-DD HH24:MI:SS.FF'), TO_TIMESTAMP('WRONG' DEFAULT '22-Sep-01 01:00:00' ON CONVERSION ERROR,'YYYY-mon-DD HH24:MI:SS.FF'), TO_TIMESTAMP('WRONG' DEFAULT '23-Sep-01 01:00:00' ON CONVERSION ERROR,'YYYY-mon-DD HH24:MI:SS.FF'));
        insert into {self.tb_name} values(TO_TIMESTAMP('WRONG' DEFAULT '19-Sep-01 01:00:00' ON CONVERSION ERROR,'YYYY-mon-DD HH24:MI:SS.FF'), TO_TIMESTAMP('WRONG' DEFAULT '20-Sep-01 01:00:00' ON CONVERSION ERROR,'YYYY-mon-DD HH24:MI:SS.FF'), TO_TIMESTAMP('WRONG' DEFAULT '21-Sep-01 01:00:00' ON CONVERSION ERROR,'YYYY-mon-DD HH24:MI:SS.FF'), TO_TIMESTAMP('WRONG' DEFAULT '22-Sep-01 01:00:00' ON CONVERSION ERROR,'YYYY-mon-DD HH24:MI:SS.FF'));
        insert into {self.tb_name} values(TO_TIMESTAMP('WRONG' DEFAULT '18-Sep-01 01:00:00' ON CONVERSION ERROR,'YYYY-mon-DD HH24:MI:SS.FF'), TO_TIMESTAMP('WRONG' DEFAULT '19-Sep-01 01:00:00' ON CONVERSION ERROR,'YYYY-mon-DD HH24:MI:SS.FF'), TO_TIMESTAMP('WRONG' DEFAULT '20-Sep-01 01:00:00' ON CONVERSION ERROR,'YYYY-mon-DD HH24:MI:SS.FF'), TO_TIMESTAMP('WRONG' DEFAULT '21-Sep-01 01:00:00' ON CONVERSION ERROR,'YYYY-mon-DD HH24:MI:SS.FF'));
        insert into {self.tb_name} values(TO_TIMESTAMP('WRONG' DEFAULT '17-Sep-01 01:00:00' ON CONVERSION ERROR,'YYYY-mon-DD HH24:MI:SS.FF'), TO_TIMESTAMP('WRONG' DEFAULT '18-Sep-01 01:00:00' ON CONVERSION ERROR,'YYYY-mon-DD HH24:MI:SS.FF'), TO_TIMESTAMP('WRONG' DEFAULT '19-Sep-01 01:00:00' ON CONVERSION ERROR,'YYYY-mon-DD HH24:MI:SS.FF'), TO_TIMESTAMP('WRONG' DEFAULT '20-Sep-01 01:00:00' ON CONVERSION ERROR,'YYYY-mon-DD HH24:MI:SS.FF'));
        '''
        sql_res = self.pri_sh.execut_db_sql(sql)
        self.log.info(sql_res)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, sql_res,
                      f'执行失败：{text}')
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, sql_res,
                      f'执行失败：{text}')
        self.assertIn(self.constant.CREATE_INDEX_SUCCESS, sql_res,
                      f'执行失败：{text}')
        self.assertIn('INSERT 0 1', sql_res, f'执行失败：{text}')
        self.assertEqual(sql_res.count('INSERT 0 1'), 4, f'执行失败：{text}')

        text = '-----step2:关闭enable_seqsca;expect:成功-----'
        self.log.info(text)
        res = self.pri_sh.execute_gsguc(
            'set', self.constant.GSGUC_SUCCESS_MSG, 'enable_seqscan=off')
        self.log.info(res)
        self.assertTrue(res)
        res = self.pri_sh.restart_db_cluster()
        self.log.info(res)

        text = '-----step3:执行单次查询;expect:成功-----'
        self.log.info(text)
        sql = f'''
              select to_timestamp(time1 default '2023-04-14 00:15:00.000000' on conversion error,'YYYY-mm-DD HH24:MI:SS.FF'), to_timestamp(time2 default '2023-04-14 00:15:00.000000' on conversion error,'YYYY-mm-DD HH24:MI:SS.FF'), to_timestamp(time3 default '2023-04-14 00:15:00.000000' on conversion error,'YYYY-mm-DD HH24:MI:SS.FF'), to_timestamp(time4 default '2023-04-14 00:15:00.000000' on conversion error,'YYYY-mm-DD HH24:MI:SS.FF')
from {self.tb_name}
        '''
        res = self.pri_sh.execut_db_sql(sql)
        self.log.info(res)
        self.assertIn('4 rows', res, f'执行失败：{text}')

        text = '-----step4:循环执行10w次查询，记录耗时;expect:成功-----'
        self.log.info(text)
        sql = f'''
        do \$\$
        declare
          i int;
          start_time timestamp;
          end_time timestamp;
        begin
          start_time := clock_timestamp();
          for i in 1..100000 loop
            execute \$cmd\$ select to_timestamp(time1 default '2023-04-14 00:15:00.000000' on conversion error,'YYYY-mm-DD HH24:MI:SS.FF'), to_timestamp(time2 default '2023-04-14 00:15:00.000000' on conversion error,'YYYY-mm-DD HH24:MI:SS.FF'), to_timestamp(time3 default '2023-04-14 00:15:00.000000' on conversion error,'YYYY-mm-DD HH24:MI:SS.FF'), to_timestamp(time4 default '2023-04-14 00:15:00.000000' on conversion error,'YYYY-mm-DD HH24:MI:SS.FF')
from {self.tb_name}\$cmd\$;
            if i % 10000 = 0 then
              raise notice '执行第 % 次查询', i;
            end if;
          end loop;
          end_time := clock_timestamp();
          raise notice '查询执行时间: % 秒',
          extract(epoch from (end_time - start_time));
        end \$\$;
        '''
        self.log.info(sql)
        res = self.pri_sh.execut_db_sql(sql)
        self.log.info(res)
        self.assertIn(self.constant.CREATE_ANONYMOUS_BLOCK_SUCCESS_MSG, res,
                      f'执行失败：{text}')
        find_time = re.search(r"查询执行时间: ([\d.]+) 秒", res)
        time = [round(float(find_time.group(1)), 1) if find_time else None]
        self.log.info(time)
        self.log.info('此处对比数据为需求测试时oracle侧测试结果，若高于此结果，性能达标')

    def tearDown(self):
        text = '-----step5:清理环境; expect:成功-----'
        self.log.info(text)
        sql = f'drop table if exists {self.tb_name} cascade;'
        drop_res = self.pri_sh.execut_db_sql(sql)
        self.log.info(drop_res)
        restore_res = self.pri_sh.execute_gsguc(
            'set', self.constant.GSGUC_SUCCESS_MSG, 'enable_seqscan=on')
        self.log.info(restore_res)
        res = self.pri_sh.restart_db_cluster()
        self.log.info(res)

        self.assertIn(self.constant.DROP_TABLE_SUCCESS, drop_res,
                      f'执行失败：{text}')
        self.assertTrue(restore_res)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
